﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
using System.Web;
using System.Web.Caching;


using LMD.Constant;
using LMD.Lib.Oracle;

namespace Cached
{
    /// <summary>
    /// Summary description for CompanyReport_Pembayaran
    /// </summary>
    public class CompanyReport_Pembayaran : CacheAbstraction
    {
        public DataTable GetPembayaranRekap(int iCompanyId, DateTime dtmStart, DateTime dtmEnd)
        {
            return GetPembayaranRekap(iCompanyId, dtmStart, dtmEnd, false);
        }
        public DataTable GetPembayaranRekap(int iCompanyId, DateTime dtmStart, DateTime dtmEnd, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
                KCache.V_REPORT_PEMBAYARAN_REKAP, 
                iCompanyId.ToString(),
                dtmStart.ToShortDateString(),
                dtmEnd.ToShortDateString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib(Connection.ORACONN_DB03);

                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCompanyId;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStart";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                lsParams[lsParams.Count - 1].Value = dtmStart;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEnd";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                lsParams[lsParams.Count - 1].Value = dtmEnd;

                String sxQuery =
                    @"
                    SELECT
                        sum(X.DEBET * X.RATE) DEBET,
                        sum(X.KOREKSI * X.RATE) KOREKSI,
                        CAST(TO_CHAR(X.PERIODE_TAGIHAN, 'MM') as NUMBER) MONTH,
                        CAST(TO_CHAR(X.PERIODE_TAGIHAN, 'YYYY') as NUMBER) YEAR
                    FROM    
                    (
                        SELECT 
                            DECODE(f1.ACURRENCY, 'Rp', 1, 
                                (
                                    SELECT
                                        cur.frate
                                    FROM
                                        trd_curr cur
                                    WHERE   
                                        CAST(to_char(cur.DDATE, 'MM') as number) = CAST(to_char(f1.PERIODE_TAGIHAN, 'MM') as number)  
                                        and CAST(to_char(cur.DDATE, 'YYYY') as number) = CAST(to_char(f1.PERIODE_TAGIHAN, 'YYYY') as number)
                                        AND cur.ACURRENCY = 'USD'
                                        AND ROWNUM = 1
                                )
                            ) RATE,        
                            f1.PERIODE_TAGIHAN,
                            f1.DEBET, 
                            f1.KOREKSI
                        FROM        
                            FACT_BILL_COLLECTION2_REMMY_RK f1
                            join sni.COMPANY t1 on t1.COMPANY_NO = f1.KODE_PELANGGAN
                        where 
                            t1.COMPANY_ID = :prmID
                            and TRUNC(PERIODE_TAGIHAN) >= TRUNC(:prmStart)
                            and TRUNC(PERIODE_TAGIHAN) < TRUNC(:prmEnd + 1)
                    ) X
                    GROUP BY 
                        X.PERIODE_TAGIHAN
                    ";

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public DataTable GetPembayaranDetail(int iCompanyId, DateTime dtmStart, DateTime dtmEnd)
        {
            return GetPembayaranDetail(iCompanyId, dtmStart, dtmEnd, false);
        }
        public DataTable GetPembayaranDetail(int iCompanyId, DateTime dtmStart, DateTime dtmEnd, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
                KCache.V_REPORT_PEMBAYARAN_DETAIL,
                iCompanyId.ToString(),
                dtmStart.ToShortDateString(),
                dtmEnd.ToShortDateString());

            if(bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib(Connection.ORACONN_DB03);

                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCompanyId;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStart";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                lsParams[lsParams.Count - 1].Value = dtmStart;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEnd";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                lsParams[lsParams.Count - 1].Value = dtmEnd;

                String sxQuery =
                    @"
                    SELECT 
                        Y.ACURRENCY,
                        TO_CHAR(Y.PERIODE_TAGIHAN, 'MON YYYY') PERIODE,    
                        Y.DEBET,
                        Y.KOREKSI,
                        Y.KREDIT,
                        DECODE(Y.DEBET_KOREKSI, 0, 0, ROUND((Y.KREDIT/Y.DEBET_KOREKSI)*100,2)) PERCENT_K,
                        Y.KREDIT_30,
                        DECODE(Y.DEBET_KOREKSI, 0, 0, ROUND((Y.KREDIT_30/Y.DEBET_KOREKSI)*100,2)) PERCENT_K30,
                        Y.KREDIT_31_45,
                        DECODE(Y.DEBET_KOREKSI, 0, 0, ROUND((Y.KREDIT_31_45/Y.DEBET_KOREKSI)*100,2)) PERCENT_K31,
                        Y.KREDIT_46_60,
                        DECODE(Y.DEBET_KOREKSI, 0, 0, ROUND((Y.KREDIT_46_60/Y.DEBET_KOREKSI)*100,2)) PERCENT_K46,
                        Y.KREDIT_61_90,
                        DECODE(Y.DEBET_KOREKSI, 0, 0, ROUND((Y.KREDIT_61_90/Y.DEBET_KOREKSI)*100,2)) PERCENT_K61,
                        Y.KREDIT_91,
                        DECODE(Y.DEBET_KOREKSI, 0, 0, ROUND((Y.KREDIT_91/Y.DEBET_KOREKSI)*100,2)) PERCENT_K91,
                        Y.SALDO
                    FROM    
                        (    
                            SELECT 
                                X.ACURRENCY,
                                X.PERIODE_TAGIHAN,    
                                sum(X.DEBET) DEBET,
                                sum(X.KOREKSI) KOREKSI,
                                sum(X.KREDIT) KREDIT,
                                sum(X.KREDIT_30) KREDIT_30,
                                sum(X.KREDIT_31_45) KREDIT_31_45,
                                sum(X.KREDIT_46_60) KREDIT_46_60,
                                sum(X.KREDIT_61_90) KREDIT_61_90,
                                sum(X.KREDIT_91) KREDIT_91,
                                sum(X.SALDO) SALDO,
                                sum(X.DEBET)+sum(X.KOREKSI) DEBET_KOREKSI
                            FROM    
                            (
                                SELECT 
                                    f1.ACURRENCY,
                                    f1.PERIODE_TAGIHAN,
                                    f1.DEBET, 
                                    f1.KREDIT,
                                    f1.KREDIT_30,
                                    f1.KREDIT_31_45,
                                    f1.KREDIT_46_60,
                                    f1.KREDIT_61_90,
                                    f1.KREDIT_91,
                                    ((f1.DEBET+f1.KOREKSI)-f1.KREDIT) SALDO, 
                                    f1.KOREKSI
                                FROM        
                                    FACT_BILL_COLLECTION2_REMMY_RK f1
                                    join sni.COMPANY t1 on t1.COMPANY_NO = f1.KODE_PELANGGAN
                                where 
                                    t1.COMPANY_ID = :prmID
                                    and TRUNC(PERIODE_TAGIHAN) >= TRUNC(:prmStart)
                                    and TRUNC(PERIODE_TAGIHAN) < TRUNC(:prmEnd + 1)
                            ) X
                            GROUP BY 
                                X.ACURRENCY,
                                X.PERIODE_TAGIHAN
                        ) Y
                        order by Y.PERIODE_TAGIHAN desc
                    ";

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }        

        public override String GetCacheFilename()
        {
            return KCache.Filename.COMPANY_REPORT_VIEWS;
        }
    }
}